package dao.concret;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import dao.AbstractTable;
import dao.DAO;
import dao.tables.PERSONNE;

public class PersonneDAO extends DAO<PERSONNE> {

	@Override
	public PERSONNE find(long id) {
		PERSONNE pers = new PERSONNE();
		try {
			Statement stmt = this.connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
			ResultSet result = stmt.executeQuery("SELECT * FROM PERSONNE WHERE IDPERSONNE = " + id);
			if (result.first()) {
				pers = new PERSONNE(result.getLong("IDPERSONNE"), result.getString("NOMPERSONNE"),
					result.getString("PRENOMPERSONNE"), result.getString("CIVILITEPERSONNE"),
					result.getDate("DATENAISSANCEPERSONNE"));
			}
			stmt.close();
			result.close();
			stmt = null;
			result = null;
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		}
		return pers;
	}

	@Override
	public PERSONNE create(AbstractTable at) {
		PERSONNE obj = (PERSONNE) at;

		try {
			PreparedStatement prepare = this.connect.prepareStatement("INSERT INTO INSTRUMENT"
				+ " (NOMPERSONNE, PRENOMPERSONNE, CIVILITEPERSONNE, DATENAISSANCEPERSONNE)"
				+ " VALUES (?, ?, ?, ?)");
			prepare.setString(1, obj.getNom());
			prepare.setString(2, obj.getPrenom());
			prepare.setString(3, obj.getCivilite());
			prepare.setDate(4, obj.getDateDeNaissance());
			prepare.executeUpdate();

			ResultSet result = this.connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_UPDATABLE).executeQuery(
				"SELECT SEQ_PERS_IDPERS.currval AS id FROM dual");
			result.first();
			long id = result.getLong("id");

			obj = this.find(id);
			prepare.close();
			result.close();
			prepare = null;
			result = null;
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		}
		return obj;
	}

	@Override
	public PERSONNE update(AbstractTable at) {
		PERSONNE obj = (PERSONNE) at;
		try {
			PreparedStatement prepare = this.connect
				.prepareStatement("UPDATE PERSONNE SET NOMPERSONNE = ? , PRENOMPERSONNE = ?, CIVILITEPERSONNE = ?,"
					+ " DATENAISSANCEPERSONNE = ? WHERE IDPERSONNE = ?");
			prepare.setString(1, obj.getNom());
			prepare.setString(2, obj.getPrenom());
			prepare.setString(3, obj.getCivilite());
			prepare.setDate(4, obj.getDateDeNaissance());
			prepare.setLong(5, obj.getId());
			prepare.executeUpdate();

			prepare.close();
			prepare = null;
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		}
		return obj;
	}

	@Override
	public PERSONNE delete(AbstractTable at) {
		PERSONNE obj = (PERSONNE) at;
		try {
			Statement stmt = this.connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
			stmt.executeUpdate("DELETE FROM PERSONNE WHERE id = " + obj.getId());

			stmt.close();
			stmt = null;
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		}
		return obj;
	}

	@Override
	public ArrayList<PERSONNE> list() {
		ArrayList<PERSONNE> list = new ArrayList<PERSONNE>();
		try {
			Statement stmt = this.connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
			ResultSet result = stmt.executeQuery("SELECT IDPERSONNE FROM PERSONNE");
			while (result.next())
				list.add(this.find(result.getLong("IDPERSONNE")));

			stmt.close();
			result.close();
			stmt = null;
			result = null;
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		}
		return list;
	}

}
